<?php
/**
 * 订单order model
 * @author SongKejing 2012.12.27
 */
class OrderModel extends BaseModel{
	
	/**
	 * 根据条件查询订单
	 * @param sting $begin_time 开始时间
	 * @param string $end_time 结束时间
	 * @param string $customer_name 客户名称
	 * @param string $tel_num 手机号码
	 * @param int $order_status 订单状态
	 * @param int $current_page 当前页
	 * @param int $page_step 每页显示数
	 * @param int $page_count 页总数
	 * @return array
	 */
	public function getCustomerOrder($begin_time, $end_time, $customer_name, $tel_num, $order_status, $current_page=1, $page_step=12, $page_count=0){		
		//默认展现待退款的订单
		if (empty($customer_name) && empty($tel_num)){
			return $this->getServiceOrders($begin_time, $end_time, $customer_name, $tel_num, $order_status, 0, $current_page, $page_step, $page_count);
		}else{		
			if(empty($customer_name)){
				$sql = "SELECT id, name, mobile, address, province, city FROM v1_user WHERE mobile='$tel_num' LIMIT 1";
			}else{
				$sql = "SELECT id, name, mobile, address, province, city FROM v1_user WHERE name='$customer_name'";
			}
			$users = $this->DBSelect($sql);
			if(empty($users)){
				return array('result'=>FALSE, 'msg'=>'不存在此用户，请检查后重新查询！');
			}
			$users_sql = '';
			foreach ($users as $user_each){
				if ($users_sql == ''){
					$users_sql = " UserID='".$user_each['id']."'";
				}else{
					$users_sql .= " OR UserID='".$user_each['id']."'";
				}
			}		
			$begin = $page_step*($current_page-1);	
			$orders_sql = "SELECT * FROM v1_order_status WHERE status='$order_status' AND (".$users_sql.") ORDER BY OrderNumber ASC LIMIT $begin, $page_step";
			$current_page_orders = $this->DBSelect($orders_sql);
			//获取所有订单状态
			$order_status_sql = "SELECT * FROM t_config WHERE name='order_status'";
			$order_status_array = $this->DBSelect($order_status_sql);
			$status_array = array();
			//将状态和状态说明改造为key-value形式，用于赋值
			foreach($order_status_array as $order_status_each){
				$status_array[$order_status_each['value']] = $order_status_each['remark'];
			}
			//循环添加用户资料组装array
			foreach ($users as $user_each){
				foreach($current_page_orders as $key=>$page_order_each){
					if($user_each['id']==$page_order_each['UserID']){
						$current_page_orders[$key] = array_merge($current_page_orders[$key], $user_each);
					}
					//判断订单状态，用于退款操作
					if($page_order_each['status']==13){
						$current_page_orders[$key]['refund'] = true;
					}else{
						$current_page_orders[$key]['refund'] = false;
					}
					if(array_key_exists($current_page_orders[$key]['status'], $status_array)){
						$current_page_orders[$key]['status_name'] = $status_array[$current_page_orders[$key]['status']];
					}else{
						$current_page_orders[$key]['status_name'] = '无此状态';
					}
				}
			}
			if($page_count==0){
				$page_count_sql = "SELECT COUNT(OrderNumber) all_count FROM v1_order_status WHERE status='$order_status' AND (".$users_sql.")";
				$page_count = $this->DBSelect($page_count_sql);
				$page_count = ceil($page_count[0]['all_count']/$page_step);
			}
			$page_array = array();
			for($i=1; $i<=$page_count; $i++){
				array_push($page_array, $i);
			}
		}
		return array(
			'result'=>TRUE,
			'msg'=>'查询成功！',
			'page_count'=>$page_count,
			'page_step'=>$page_step,
			'current_page'=>$current_page,
			'page_array'=>$page_array,
			'orders'=>$current_page_orders,	
		);
	}

	/**
	 * 导出Excel报表
	 * @param sting $begin_time 开始时间
	 * @param string $end_time 结束时间
	 * @param string $customer_name 客户名称
	 * @param string $tel_num 手机号码
	 * @param int $order_status 订单状态
	 * @param string $export_ids
	 * @return array
	 */
	public function exportOrders($begin_time, $end_time, $name, $tel_num, $order_status, $export_ids){
		//当export_ids为空的时候，则导出查询结果，否则只导出指定的id
		if(empty($export_ids)){
			//查询出所有的符合条件的记录
			$service_id = $_SESSION['uid'];
			if(empty($name) && empty($tel_num)){
				$sql = "SELECT * FROM t_service_order WHERE service_id='$service_id'";
				$service_orders = $this->DBSelect($sql);
				$orders_string = '';
				foreach ($service_orders as $each_order){
					if($orders_string==''){
						$orders_string = $each_order['order_id'];
					}else{
						$orders_string .= ', '.$each_order['order_id'];
					}
				}
				$return_orders_sql = "SELECT * FROM v1_order_status WHERE OrderNumber in($orders_string)";
				$orders_result = $this->DBSelect($return_orders_sql);
				//循环拼接数组
				foreach ($orders_result as $key=>$each_order){
						//拼接数组
						$uid_each = $each_order['UserID'];
						$user_each_sql = "SELECT name, mobile, address, province, city FROM v1_user WHERE id='$uid_each' LIMIT 1";
						$user_each = $this->DBSelect($user_each_sql);
						$orders_result[$key] = array_merge($each_order, $user_each[0]);
				}
			}else{
				if(empty($name)){
					$sql = "SELECT id, name, mobile, address, province, city FROM v1_user WHERE mobile='$tel_num' LIMIT 1";
				}else{
					$sql = "SELECT id, name, mobile, address, province, city FROM v1_user WHERE name='$name'";
				}
				$users = $this->DBSelect($sql);
				if(empty($users)){
					return array('result'=>FALSE, 'msg'=>'不存在此用户，请检查后重新查询！');
				}
				$users_sql = '';
				foreach ($users as $user_each){
					if ($users_sql == ''){
						$users_sql = " UserID='".$user_each['id']."'";
					}else{
						$users_sql .= " OR UserID='".$user_each['id']."'";
					}
				}
				$orders_sql = "SELECT * FROM v1_order_status WHERE status='$order_status' AND (".$users_sql.") ORDER BY OrderNumber ASC";
				$orders_result = $this->DBSelect($orders_sql);
				//循环添加用户资料组装array
				foreach ($users as $user_each){
					foreach($orders_result as $key=>$page_order_each){
						if($user_each['id']==$page_order_each['UserID']){
							$orders_result[$key] = array_merge($orders_result[$key], $user_each);
						}
					}
				}
			}			
		}else{
			$orders_sql = "SELECT * FROM v1_order_status WHERE OrderNumber in($export_ids)";
			$orders_result = $this->DBSelect($orders_sql);
			foreach ($orders_result as $key=>$order_each){
				$uid_each = $order_each['UserID'];
				$user_each_sql = "SELECT name, mobile, address, province, city FROM v1_user WHERE id='$uid_each' LIMIT 1";
				$user_each = $this->DBSelect($user_each_sql);
				$orders_result[$key] = array_merge($order_each, $user_each[0]);
			}					
		}
		$orders_result = array(
				'title' => array(
								'OrderNumber' => '订单号',
					            'status' => '状态',
					            'UserID' => '用户ID',
					            'OrderPrice' => '订单金额',
					            'ctime' => '下单日期',
					            'name' => '用户名',
					            'mobile' => '手机号',
					            'address' => '地址',
					            'province' => '省',
					            'city' => '市'
				),
				'values' => $orders_result
		);
		saveToExcel($name.$tel_num.'_orders', $orders_result);
	}
	
	/**
	 * 更新订单状态
	 * @param int $order_id
	 * @param int $status
	 * @return boolean
	 */
	public function updateOrderStatus($order_id, $status){
		return true;
		$sql = "UPDATE v1_order_status SET status='$status' WHERE OrderNumber='$order_id' LIMIT 1";
		if($this->DBUpdate($sql)){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 根据条件查询订单
	 * @param sting $begin_time 开始时间
	 * @param string $end_time 结束时间
	 * @param string $customer_name 客户名称
	 * @param string $tel_num 手机号码
	 * @param int $order_status 订单状态
	 * @param int $current_page 当前页
	 * @param int $page_step 每页显示数
	 * @param int $page_count 页总数
	 * @param int $user_id 为空时默认取当前登录session uid，否则取对应的uid分配的orders
	 * @return array
	 */
	public function getServiceOrders($begin_time, $end_time, $customer_name, $tel_num, $order_status, $user_id=0, $current_page=1, $page_step=12, $page_count=0){
		if($user_id==0){
			$service_id = $_SESSION['uid'];
		}else{
			$service_id = $user_id;
		}
		//处理订单分配
		$this->dealOrdersForServices(array(13));
		$begin = $page_step*($current_page-1);
		$sql = "SELECT * FROM t_service_order WHERE service_id='$service_id' AND is_deal=0 LIMIT $begin, $page_step";
		$order_ids = $this->DBSelect($sql);
		if(empty($order_ids)){
			return array(
							'result'=>FALSE, 
							'msg'=>'用户名和手机号至少有一个查询条件',
							'page_count'=>$page_count,
							'page_step'=>$page_step,
							'current_page'=>$current_page,
							'page_array'=>array(1),
							'order_count'=>0,
							'orders'=>array()
						);
		}else{
			//转移1天前已经完成的数据到历史表
			$point_unix_time_stamp = time()-24*3600;
			//获取需要转移的id
			$move_ids_sql = "SELECT id FROM t_service_order WHERE service_id='$service_id' AND is_deal=1 AND create_time<$point_unix_time_stamp";
			$move_ids = $this->DBSelect($move_ids_sql);
			if(!empty($move_ids)){
				$move_ids_string = '';
				foreach ($move_ids as $each_id){
					if($move_ids_string==''){
						$move_ids_string = $each_id['id'];
					}else{
						$move_ids_string .= ','.$each_id['id'];
					}
				}
				//移1天前的数据
				$move_his_sql = "INSERT INTO t_service_order_his (service_id, order_id, is_deal, create_time, sign_uid) SELECT service_id, order_id, is_deal, create_time, sign_uid FROM t_service_order WHERE id IN ($move_ids_string)";
				$this->DBInsert($move_his_sql);
				//删除1天前的数据
				$delete_his_sql = "DELETE FROM t_service_order WHERE id IN ($move_ids_string)";
				$this->DBDelete($delete_his_sql);
			}			
			//依据订单号查询所有的订单信息
			$orders_string = '';
			foreach ($order_ids as $each_order){
				if($orders_string==''){
					$orders_string = $each_order['order_id'];
				}else{
					$orders_string .= ', '.$each_order['order_id'];
				}
			}
			$return_orders_sql = "SELECT * FROM v1_order_status WHERE OrderNumber in($orders_string)";
			$return_orders = $this->DBSelect($return_orders_sql);
			//循环拼接数组
			foreach ($return_orders as $key=>$each_order){
				//拼接数组
				$uid_each = $each_order['UserID'];
				$user_each_sql = "SELECT name, mobile, address, province, city, area FROM v1_user WHERE id='$uid_each' LIMIT 1";
				$user_each = $this->DBSelect($user_each_sql);
				$return_orders[$key] = array_merge($each_order, $user_each[0]);
				//获取订单状态值
				$order_config_info = $this->getOrderConfigInfo($order_status);
				$return_orders[$key]['status_name'] = $order_config_info[0]['remark'];
				if($order_status==13){
					$return_orders[$key]['refund'] = TRUE;
				}else{
					$return_orders[$key]['refund'] = FALSE;
				}				
			}
			//重置$page_count
			$page_count_sql = "SELECT COUNT(id) all_count FROM t_service_order WHERE service_id='$service_id' AND is_deal=0";
			$page_count = $this->DBSelect($page_count_sql);
			$order_count = $page_count[0]['all_count'];
			$page_count = ceil($order_count/$page_step);
			$page_array = array();
			for($i=1; $i<=$page_count; $i++){
				array_push($page_array, $i);
			}
			return array(
						'result'=>TRUE, 
						'msg'=>'查询成功',
						'page_count'=>$page_count,
						'page_step'=>$page_step,
						'current_page'=>$current_page,
						'page_array'=>$page_array,
						'order_count'=>$order_count,
						'orders'=>$return_orders
					);
		}
	}
	
	/**
	 * 获取订单配置信息
	 * @param mixed $status 默认-9999，获取所有的状态，否则则获取当前statsu对应的状态，为数组则查询对应的status
	 * @return array
	 */
	public function getOrderConfigInfo($status=-9999){		
		if(is_array($status)){
			$sql = "SELECT * FROM t_config WHERE name='order_status' AND status IN(".implode(',', $status).")";
		}else{
			if($status==-9999){
				$sql = "SELECT * FROM t_config WHERE name='order_status'";
			}else{
				$sql = "SELECT * FROM t_config WHERE name='order_status' AND status='$status' LIMIT 1";
			}
		}
		return $this->DBSelect($sql);
	}
	
	/**
	 * 处理订单分配
	 * @param array $status
	 * @return boolean
	 */
	public function dealOrdersForServices($status=array(13)){		
		$status_string = implode(',', $status);
		//每次获取50条未处理的订单
		$orders_sql = "SELECT * FROM v1_order_status WHERE status IN($status_string) ORDER BY OrderNumber ASC LIMIT 50";
		$orders = $this->DBSelect($orders_sql);
		if(empty($orders)){
			return TRUE;
		}else{
			//获取所有当天登录的客服
			$services_sql = "SELECT u.user_id FROM t_user u, t_user_role u_r WHERE u.user_id=u_r.user_id AND u_r.role_id=3 AND DATE_FORMAT(u.login_time, '%Y%m%d')=DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y%m%d')";
			$services = $this->DBSelect($services_sql);
			$services_count = count($services);
			if($services_count>0){
				//循环处理已经获取的订单
				foreach ($orders as $each_order){
					$each_order_id = $each_order['OrderNumber'];
					//判断当前order_id是否已经分配，如果已经分配则continue
					$check_order_sql = "SELECT id FROM t_service_order WHERE order_id='$each_order_id' LIMIT 1";
					$check_order = $this->DBSelect($check_order_sql);
					if(empty($check_order)){
						//随即选取客服id
						$service_id = $services[mt_rand(0, $services_count-1)]['user_id'];
						$insert_order_sql = "INSERT INTO t_service_order (service_id, order_id, create_time) VALUES($service_id, $each_order_id, UNIX_TIMESTAMP())";
						$this->DBInsert($insert_order_sql);
					}else{
						continue;					
					}
				}
				return TRUE;
			}else{
				return FALSE;
			}			
		}		
	}
	
	/**
	 * 重新分配任务
	 * @param string $id_str t_service_order主键id字符串：1,2,3
	 * @param int $service_id
	 * @return boolean
	 */
	public function signJob($id_str, $service_id){
		$sign_uid = $_SESSION['uid'];
		$sql = "UPDATE t_service_order SET service_id='$service_id' AND sign_uid='$sign_uid' WHERE id IN ($id_str)";
		return $this->DBUpdate($sql);
	}
	
	/**
	 * 更新分配给客服的订单状态
	 * @param int $service_id 客服id
	 * @param int $order_id 订单id
	 * @return boolean
	 */
	public function updateServiceOrderInfo($service_id, $order_id){
		$sql = "UPDATE t_service_order SET is_deal=1 WHERE service_id='$service_id' AND order_id='$order_id' LIMIT 1";
		return $this->DBUpdate($sql);
	}

}
?>